﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using AutoMapper;
using System.Data;

namespace DAO
{
    public class SachRepository
    {
        public static SACH CreateSachEFFromSachDTO(SachDTO sachDTO)
        {
            SACH sachEF = new SACH();
            Mapper.CreateMap<SachDTO, SACH>();
            sachEF=Mapper.Map<SachDTO, SACH>(sachDTO);
            return sachEF;
        }
        public static SachDTO CreateSachDTOFromSachEF(SACH sachEF)
        {
            SachDTO sachDTO = new SachDTO();
            Mapper.CreateMap<SACH, SachDTO>();
            sachDTO = Mapper.Map<SACH, SachDTO>(sachEF);
            return sachDTO;
        }

        

        public void ThemSach(SachDTO sachDTO)
        {
            try
            {
                QLTVEntities contex = ContextFactory.GetInstance();
                //SACH sachEF = CreateSachEFFromSachDTO(sachDTO);
                SACH sachEF = new SACH();
                sachEF.MaSach = sachDTO.MaSach;
                sachEF.MaSoSach = sachDTO.MaSoSach;
                sachEF.TenSach = sachDTO.TenSach;
                sachEF.MaTacGia = sachDTO.MaTacGia;
                sachEF.MaTheLoai = sachDTO.MaTheLoai;
                sachEF.NamXuatBan = sachDTO.NamXuatBan;
                sachEF.NhaXuatBan = sachDTO.NhaXuatBan;
                sachEF.NgayNhap = sachDTO.NgayNhap;
                sachEF.NguoiTiepNhan = sachDTO.NguoiTiepNhan;
                sachEF.DonGia = sachDTO.DonGia;
                sachEF.TinhTrangSach = sachDTO.TinhTrangSach;
                sachEF.DaXoa = sachDTO.DaXoa;

                contex.AddToSACHList(sachEF);
                contex.SaveChanges();
            }
            catch (System.Exception)
            {
            }
        }

        public int LayMaSachMax()
        {
            QLTVEntities context = ContextFactory.GetInstance();
            int maSach = context.SACHList.Max(s => s.MaSach);

            return maSach;
        }

        public int LayMaSachTheoMaSoSach(string maso)
        {
            QLTVEntities context = ContextFactory.GetInstance();
            var query = from sach in context.SACHList
                        where sach.MaSoSach == maso
                        select sach;
            SACH sachTemp = query.First();
            int maNV = sachTemp.MaSach;
            return maNV;
        }

        public SachDTO LayThongTinSach(int masach)
        {
            SachDTO sachDTO = new SachDTO();
            try
            {
                QLTVEntities context = ContextFactory.GetInstance();
                var query = from sach in context.SACHList
                            where sach.MaSach == masach
                            select sach;
                SACH sachTemp = query.First();
                if (sachTemp != null)
                {
                    sachDTO = CreateSachDTOFromSachEF(sachTemp);
                }
            }
            catch (System.Exception)
            {

            }
            return sachDTO;
        }        
               
        
        public DataTable TraCuuSach(string maSoSach, string tenSach, string tenTacGia, int maTheLoai, int maNXB, int namXB)
        {
            DataTable dbTraCuu = new DataTable();
            QLTVEntities context = new QLTVEntities();
            var query = from sach in context.SACHList
                        from theLoai in context.THE_LOAIList
                        from tacGia in context.TAC_GIAList
                        from nhaXB in context.NHA_XUAT_BANList
                        from tinhTrang in context.TINH_TRANGList
                        where sach.MaTheLoai == theLoai.MaTheLoai && sach.MaTacGia == tacGia.MaTacGia
                        && sach.NhaXuatBan == nhaXB.MaNhaXuatBan && sach.TinhTrangSach == tinhTrang.MaTinhTrang
                        select new
                        {
                            sach.MaSach,
                            sach.MaSoSach,
                            sach.TenSach,
                            theLoai.MaTheLoai,
                            theLoai.TenTheLoai,
                            tacGia.TenTacGia,
                            nhaXB.MaNhaXuatBan,
                            nhaXB.TenNhaXuatBan,
                            sach.NamXuatBan,
                            tinhTrang.TenTinhTrang
                        };
            if (maSoSach != "")
                query = query.Where(sach => sach.MaSoSach.Contains(maSoSach));
            if (tenSach != "")
               query = query.Where(sach => sach.TenSach.Contains(tenSach));
            if (tenTacGia != "")
                query = query.Where(tacGia => tacGia.TenTacGia.Contains(tenTacGia));
            if (maTheLoai != 0)
                query= query.Where(sach => sach.MaTheLoai == maTheLoai);
            if (maNXB != 0)
                query = query.Where(sach => sach.MaNhaXuatBan == maNXB);
            if (namXB != 0)
                query = query.Where(sach => sach.NamXuatBan == namXB);
            dbTraCuu.Columns.Add("MaSach");
            dbTraCuu.Columns.Add("TenSach");
            dbTraCuu.Columns.Add("TenTheLoai");
            dbTraCuu.Columns.Add("TenTacGia");
            dbTraCuu.Columns.Add("TenNhaXuatBan");
            dbTraCuu.Columns.Add("TenTinhTrang");
            dbTraCuu.Columns.Add("NamXuatBan");

            foreach (var sach in query)
            {
                DataRow dong = dbTraCuu.NewRow();
                dong["MaSach"] = sach.MaSach;
                dong["TenSach"] = sach.TenSach;
                dong["TenTheLoai"] = sach.TenTheLoai;
                dong["TenTacGia"] = sach.TenTacGia;
                dong["TenNhaXuatBan"] = sach.TenNhaXuatBan;
                dong["TenTinhTrang"] = sach.TenTinhTrang;
                dong["NamXuatBan"] = sach.NamXuatBan;
                dbTraCuu.Rows.Add(dong);
            }
            dbTraCuu.Columns.Add("STT");
           DataProvider.ThemSoThuTu(dbTraCuu);
            return dbTraCuu;
        }
        
        public DataTable LayThongTinSachMat(int maSach)
        {
            DataTable sachMat = new DataTable();
            try
            {
                QLTVEntities context = new QLTVEntities();
                var query = from sach in context.SACHList
                            from theLoai in context.THE_LOAIList
                            where sach.MaSach == maSach && sach.MaTheLoai == theLoai.MaTheLoai
                            select new
                            {
                                sach.TenSach,
                                sach.DonGia,
                                theLoai.TenTheLoai
                            };
                sachMat.Columns.Add("Tensach");
                sachMat.Columns.Add("TenTheLoai");
                sachMat.Columns.Add("DonGia");
                DataRow dong = sachMat.NewRow();
                dong["TenSach"] = query.First().TenSach;
                dong["TenTheLoai"] = query.First().TenTheLoai;
                dong["DonGia"] = query.First().DonGia;

                sachMat.Rows.Add(dong);
            }
            catch(System.Exception)
            { }
            return sachMat;
        }

        public void CapNhatSach(SachDTO sachDTO)
        {
            try
            {
                QLTVEntities context = ContextFactory.GetInstance();
                SACH sachEF;
                var query = from sach in context.SACHList
                            where sach.MaSach == sachDTO.MaSach
                            select sach;
                sachEF = query.First();
                sachEF = CreateSachEFFromSachDTO(sachDTO);

                context.SACHList.ApplyCurrentValues(sachEF);
                context.SaveChanges();
            }
            catch (System.Exception)
            {

            }
        }
    }
}
